clear
set more off, perm
cd /Users/zachbrown/Projects/PriceTransparency/Data/

// Use medical claims
guse build/radiology_visit_1.dta, clear

// Merge on deductible, copay, and coinsurance info
tempfile tmpdata 
shell nice gunzip build/deductible_info.dta -c > `tmpdata' 
merge m:1 member_key from_date using `tmpdata' 
drop if _merge==2
drop _merge

// Merge on membership info
gen yearmo = year*100 + month(from_date)
gen coverage_medical=1
rename member_zip member_zip_clm
count

tempfile tmpdata
 shell nice gunzip build/members_clean_small.dta -c > `tmpdata'
merge m:1 member_key yearmo coverage_medical primary_ins using `tmpdata' ///
	,keep(match master) ///
	keepusing(member_key yearmo std_product_type insurance_type tier primary_ins coverage_medical member_zip payernm_long payernm_short payer_on_web payer_id mm_start_yearmo mm_end_yearmo)


// Use membership zip if claim zip is missing (could try reverse)
replace member_zip = member_zip_clm if member_zip==.
drop _merge coverage_medical primary_ins yearmo member_zip_clm


// Merge on member zip code characteristics
rename member_zip zip
merge m:1 zip using zip_info/Zip_chars.dta
drop if _merge==2
drop _merge zip_name zcta_lat zcta_long
rename zip member_zip
rename in_nh member_in_nh


// Fill in with average state characteristics
foreach var of varlist zip_median_income zip_mean_income zip_pct_hs zip_pct_ba {
	bys state: egen st_`var' = mean(`var')
	*mdesc `var'
	replace `var' = st_`var' if `var'==.
	drop st_`var'
}
drop state


// Merge on comorbidities
merge m:1 member_key using build/charlson_by_member_key.dta
drop if _merge==2
 drop _merge
drop weightch*



// Merge on providers
rename bill_prov_key prov_key
merge m:1 prov_key using build/provider_build.dta
drop if _merge==2
drop _merge prov_clinic_zip
rename prov_key bill_prov_key


// Fill in missing dummies
foreach var of varlist prov_type tier std_product_type payernm_short {
	replace `var' = 0 if `var'==.
}

// Merge on provider CW
rename bill_prov_key prov_key
merge m:1 prov_key using build/prov_cw_crosswalk.dta
drop if _merge==2
drop _merge
rename prov_key bill_prov_key
rename prov_cw_key1 bill_prov_cw_key1
rename prov_cw_key2 bill_prov_cw_key2
rename npi_id bill_npi_id
rename prvtax_id bill_prvtax_id

rename serv_prov_key prov_key
merge m:1 prov_key using build/prov_cw_crosswalk.dta
drop if _merge==2
drop _merge
rename prov_key serv_prov_key
rename prov_cw_key1 serv_prov_cw_key1
rename prov_cw_key2 serv_prov_cw_key2
rename npi_id serv_npi_id
rename prvtax_id serv_prvtax_id

// Make allowed charge variable
gen amt_allowed = amt_member_oop + amt_paid
gen amt_allowed_radio = amt_oop_radio + amt_paid_radio
gen amt_allowed_radio_max = amt_oop_radio_max + amt_paid_radio_max
label var amt_allowed "Allowed amount"
label var amt_billed "Total charge amount"


sort rec_id_radio
compress
gsave clean/radiology_visit_2.dta, replace


